package com.djf.djfpgsql.controller;

/**
 * @Author djf
 * @Date 2025/1/10
 * @Version 1.0
 */

import com.djf.djfpgsql.common.Result;
import com.djf.djfpgsql.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.djf.djfpgsql.entity.User;

import java.util.List;
import java.util.Map;

/**
 * 用户接口 - 展示MySQL和PostgreSQL的核心差异
 */
@RestController
@RequestMapping("/api/user")
public class UserController {
    @Autowired
    private UserService userService;

    // ==================== 基础操作 ====================
    
    /**
     * 查询所有用户 - MySQL
     */
    @GetMapping("/mysql")
    public Result<List<User>> listFromMySQL() {
        return Result.success(userService.listUsersFromMySQL());
    }

    /**
     * 查询所有用户 - PostgreSQL
     */
    @GetMapping("/pgsql")
    public Result<List<User>> listFromPGSQL() {
        return Result.success(userService.listUsersFromPGSQL());
    }

    /**
     * 新增用户到MySQL
     */
    @PostMapping("/mysql")
    public Result<String> addToMySQL(@RequestBody User user) {
        userService.addUserToMySQL(user);
        return Result.success("用户添加到MySQL成功");
    }

    /**
     * 新增用户到PostgreSQL
     */
    @PostMapping("/pgsql")
    public Result<String> addToPGSQL(@RequestBody User user) {
        userService.addUserToPGSQL(user);
        return Result.success("用户添加到PostgreSQL成功");
    }

    // ==================== 展示数据库差异的核心接口 ====================
    
    /**
     * 1. 字符串模糊查询差异
     * MySQL: LIKE + CONCAT
     * PostgreSQL: ILIKE + ||
     */
    @GetMapping("/search/name/{keyword}")
    public Result<Map<String, Object>> searchByName(@PathVariable String keyword) {
        List<User> mysqlResult = userService.searchUsersByNameMySQL(keyword);
        List<User> pgsqlResult = userService.searchUsersByNamePGSQL(keyword);
        
        return Result.success(Map.of(
            "mysql_result", mysqlResult,
            "postgresql_result", pgsqlResult,
            "difference", "MySQL使用LIKE+CONCAT，PostgreSQL使用ILIKE+||操作符"
        ));
    }
    
    /**
     * 2. 日期函数差异
     * MySQL: DATE_SUB(NOW(), INTERVAL n DAY)
     * PostgreSQL: NOW() - INTERVAL 'n days'
     */
    @GetMapping("/recent/{days}")
    public Result<Map<String, Object>> getRecentUsers(@PathVariable Integer days) {
        List<User> mysqlResult = userService.getRecentUsersMySQL(days);
        List<User> pgsqlResult = userService.getRecentUsersPGSQL(days);
        
        return Result.success(Map.of(
            "mysql_result", mysqlResult,
            "postgresql_result", pgsqlResult,
            "difference", "MySQL使用DATE_SUB函数，PostgreSQL使用INTERVAL语法"
        ));
    }
    
    /**
     * 3. JSON字段操作差异
     * MySQL: JSON_EXTRACT(profile, '$.city')
     * PostgreSQL: profile->>'city'
     */
    @GetMapping("/search/city/{city}")
    public Result<Map<String, Object>> findByCity(@PathVariable String city) {
        List<User> mysqlResult = userService.findUsersByCityMySQL(city);
        List<User> pgsqlResult = userService.findUsersByCityPGSQL(city);
        
        return Result.success(Map.of(
            "mysql_result", mysqlResult,
            "postgresql_result", pgsqlResult,
            "difference", "MySQL使用JSON_EXTRACT函数，PostgreSQL使用->>操作符"
        ));
    }
    
    /**
     * 4. 布尔值处理差异
     * MySQL: is_active = 1/0
     * PostgreSQL: is_active = true/false
     */
    @GetMapping("/statistics")
    public Result<Map<String, Object>> getStatistics() {
        Map<String, Object> mysqlStats = userService.getUserStatisticsMySQL();
        Map<String, Object> pgsqlStats = userService.getUserStatisticsPGSQL();
        
        return Result.success(Map.of(
            "mysql_result", mysqlStats,
            "postgresql_result", pgsqlStats,
            "difference", "MySQL布尔值用1/0表示，PostgreSQL用true/false"
        ));
    }
    
    /**
     * 5. 时间戳更新差异
     * MySQL: NOW()
     * PostgreSQL: CURRENT_TIMESTAMP
     */
    @PutMapping("/login/{id}")
    public Result<Map<String, Object>> updateLastLogin(@PathVariable Long id) {
        boolean mysqlSuccess = userService.updateLastLoginMySQL(id);
        boolean pgsqlSuccess = userService.updateLastLoginPGSQL(id);
        
        return Result.success(Map.of(
            "mysql_success", mysqlSuccess,
            "postgresql_success", pgsqlSuccess,
            "difference", "MySQL使用NOW()函数，PostgreSQL使用CURRENT_TIMESTAMP"
        ));
    }
    
    /**
     * 6. 排序和限制差异（虽然语法相同，但性能和执行计划不同）
     */
    @GetMapping("/top/{limit}")
    public Result<Map<String, Object>> getTopUsers(@PathVariable Integer limit) {
        List<User> mysqlResult = userService.getTopUsersByBalanceMySQL(limit);
        List<User> pgsqlResult = userService.getTopUsersByBalancePGSQL(limit);
        
        return Result.success(Map.of(
            "mysql_result", mysqlResult,
            "postgresql_result", pgsqlResult,
            "difference", "虽然LIMIT语法相同，但两个数据库的查询优化器和执行计划不同"
        ));
    }
}
